package nz.ac.massey.doi_information_extractor.tests;


import static org.junit.Assert.assertEquals;

import java.io.IOException;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

import nz.ac.massey.doi_information_extractor.data.Author;
import nz.ac.massey.doi_information_extractor.data.AuthorList;
import nz.ac.massey.doi_information_extractor.data.Publication;
import nz.ac.massey.doi_information_extractor.database.DatabaseImplementation;

import org.junit.After;
import org.junit.Before;
import org.junit.Test;

public class DBimplTest {

	DatabaseImplementation dbTest;
	static Connection connection;

    @Before
    public void setUp() throws IOException, SQLException, ClassNotFoundException
    {
    	dbTest = new DatabaseImplementation("src\\databases\\testDB.db");
    	connection = dbTest.getConnection("src\\databases\\testDB.db");
    }

    @After
    public void tearDown() throws SQLException
    {
    	Statement statement = connection.createStatement();
    	statement.execute("Drop table authors");
    	statement.execute("Drop table author");
    	statement.execute("Drop table publication");
    	statement.close();
    	dbTest = null;
    	connection = null;
    }

    @Test
    public void massTest() throws SQLException
    {
    	assertEquals(0,countTableRows("Author"));
    	assertEquals(0,countTableRows("Authors"));
    	assertEquals(0,countTableRows("Publication"));
    	//Check all tables are empty and that countTableRows testing method works correctly

    	AuthorList authorList = new AuthorList();
		Author author1 = new Author();
		author1.setAuthorFirstName("Mr");
		author1.setAuthorLastName("Gin");
		author1.setOrganisation("The Black Organisation");
		author1.setDepartment("Management");
		author1.setUserId("1");
		authorList.add(author1);

		Author author2 = new Author();
		author2.setAuthorFirstName("Scarlet");
		author2.setAuthorLastName("Rose");
		author2.setOrganisation("Heros Guild");
		author2.setDepartment("Hero");
		author2.setUserId("2");
		authorList.add(author2);

		Publication pub = new Publication();
    	pub.setPublicationId(1);
		pub.setDescription("description4");
		pub.setPublicationType("contributionType");
		pub.setPageNumbers("pageNumbers4");
		pub.setPublicationTitle("pubTitle4");
		pub.setQualityAssured(true);
		pub.setConfidential(true);
		pub.setResearchOrProfCom(true);
		pub.setVolumeNumber("4");
		pub.setArticleTitle("articleTitle4");
		pub.setDate("pubDate4");
		pub.setUrl("url4");
		pub.setIssn("issn4");
		pub.setDoi("doi");
		pub.setPublisher("publisher4");
		pub.setKeywords("This,is,a,test");
		pub.setConferenceLocation("Location4");
		pub.setConferenceDates("ConfDate4");
		pub.setPublisherLocation("pubLocation4");
		pub.setEditors("edittor4,edittor5");
		pub.setEdition("4");
		pub.setJournalNumber("4");

		authorList.setStaffSubmitter(1);

		pub.setAuthors(authorList);
		//Create some test data

		dbTest.write(pub);
		//Test write method. Expected 2 Rows in Author table, 2 Rows in Authors Table, 1 Row in Publication Table
    	assertEquals(2,countTableRows("Author"));
    	assertEquals(2,countTableRows("Authors"));
    	assertEquals(1,countTableRows("Publication"));
    	//Check that the row counts match what we expect

    	assertEquals(2,dbTest.getAuthors().size());
    	//Check that getAuthors works correctly

    	Publication pub2 = dbTest.read("doi");
    	//Test read works. Expected values checked below.

    	assertEquals(1,pub2.getAuthors().getStaffSubmitter());

    	assertEquals(1, pub2.getAuthors().get(0).getAuthorId());
    	assertEquals("Mr", pub2.getAuthors().get(0).getAuthorFirstName());
    	assertEquals("Gin", pub2.getAuthors().get(0).getAuthorLastName());
    	assertEquals("The Black Organisation", pub2.getAuthors().get(0).getOrganisation());
    	assertEquals("Management", pub2.getAuthors().get(0).getDepartment());
    	assertEquals("1", pub2.getAuthors().get(0).getUserId());

    	assertEquals(2, pub2.getAuthors().get(1).getAuthorId());
    	assertEquals("Scarlet", pub2.getAuthors().get(1).getAuthorFirstName());
    	assertEquals("Rose", pub2.getAuthors().get(1).getAuthorLastName());
    	assertEquals("Heros Guild", pub2.getAuthors().get(1).getOrganisation());
    	assertEquals("Hero", pub2.getAuthors().get(1).getDepartment());
    	assertEquals("2", pub2.getAuthors().get(1).getUserId());

    	assertEquals(1,pub2.getPublicationId());
    	assertEquals("description4",pub2.getDescription());
    	assertEquals("contributionType",pub2.getPublicationType());
    	assertEquals("pageNumbers4",pub2.getPageNumbers());
    	assertEquals("pubTitle4",pub2.getPublicationTitle());
		assertEquals(true,pub2.getQualityAssured());
		assertEquals(true,pub2.getConfidential());
		assertEquals(true,pub2.getResearchOrProfCom());
		assertEquals("4",pub2.getVolumeNumber());
		assertEquals("articleTitle4",pub2.getArticleTitle());
		assertEquals("pubDate4",pub2.getDate());
		assertEquals("url4",pub2.getUrl());
		assertEquals("issn4", pub2.getIssn());
		assertEquals("doi", pub2.getDoi());
		assertEquals("publisher4", pub2.getPublisher());
		assertEquals("This,is,a,test", pub2.getKeywords());
		assertEquals("Location4", pub2.getConferenceLocation());
		assertEquals("ConfDate4", pub2.getConferenceDates());
		assertEquals("pubLocation4", pub2.getPublisherLocation());
		assertEquals("edittor4,edittor5", pub2.getEditors());
		assertEquals("4", pub2.getEdition());
		assertEquals("4", pub2.getJournalNumber());
		//Checked every actual value against expected values
		//Read and Write successfully tested.

		assertEquals(1,dbTest.getDoiList().length);
		//Test whether a list of DOI's can be returned

		pub2 = new Publication();
		//Case 3. The author exists, as does the relationship with publication. We want to update author info in this case
    	authorList = new AuthorList();
		author1 = new Author();
		author1.setAuthorFirstName("Mr");
		author1.setAuthorLastName("Gin");
		author1.setOrganisation("The Black Organisation");
		author1.setDepartment("Retired");
		author1.setUserId("1");
		authorList.add(author1);

		//Case 4. Removing an existing author (Author2)

		//Case 1. Add a author that doesn't yet exist in the author table to check this case works
		Author author3 = new Author();
		author3.setAuthorFirstName("Conan");
		author3.setAuthorLastName("Edogawa");
		author3.setOrganisation("organisation3");
		author3.setDepartment("department3");
		author3.setUserId("3");
		authorList.add(author3);

		//Case 2. A author that exists within the author table, but does not have a relationship with the current publication. i.e. no entry for this particular pubID,authorID.
		Author author4 = new Author();
		author4.setAuthorFirstName("Heiji");
		author4.setAuthorLastName("Hattori");
		author4.setOrganisation("organisation4");
		author4.setDepartment("department4");
		author4.setUserId("4");
		authorList.add(author4);

		//One error identified - Might be fixed...
		PreparedStatement preparedStatement = connection.prepareStatement("INSERT INTO author (authorFirstName,authorLastName,organisation,department, userId) VALUES(?,?,?,?,?)");
		preparedStatement.setString(1, author4.getAuthorFirstName());
		preparedStatement.setString(2, author4.getAuthorLastName());
		preparedStatement.setString(3, author4.getOrganisation());
		preparedStatement.setString(4, author4.getDepartment());
		preparedStatement.setString(5, author4.getUserId());
		preparedStatement.execute();
		preparedStatement.close();

		authorList.setStaffSubmitter(3);

		pub2.setPublicationId(1);
		pub2.setDescription("UpdateTest");
		pub2.setPublicationType("UpdateTest");
		pub2.setPageNumbers("UpdateTest");
		pub2.setPublicationTitle("UpdateTest");
		pub2.setQualityAssured(false);
		pub2.setConfidential(false);
		pub2.setResearchOrProfCom(false);
		pub2.setVolumeNumber("3");
		pub2.setArticleTitle("UpdateTest");
		pub2.setDate("UpdateTest");
		pub2.setUrl("UpdateTest");
		pub2.setIssn("UpdateTest");
		pub2.setDoi("doi");
		pub2.setPublisher("publisher5");
		pub2.setKeywords("This,is,a,test");
		pub2.setConferenceLocation("Location5");
		pub2.setConferenceDates("ConfDate5");
		pub2.setPublisherLocation("pubLocation5");
		pub2.setEditors("edittor5,edittor4");
		pub2.setEdition("5");
		pub2.setJournalNumber("5");
		pub2.setAuthors(authorList);
		dbTest.write(pub2);
		//Create test data and trigger update method

		assertEquals(4,countTableRows("Author"));
		assertEquals(3,countTableRows("Authors"));
		assertEquals(1,countTableRows("Publication"));

		assertEquals(4,dbTest.getAuthors().size());
    	//Check that getAuthors works correctly

		pub = dbTest.read("doi");
		assertEquals(1,pub.getPublicationId());
		assertEquals("UpdateTest",pub.getDescription());
		assertEquals("UpdateTest",pub.getPublicationType());
		assertEquals("UpdateTest",pub.getPageNumbers());
		assertEquals("UpdateTest",pub.getPublicationTitle());
		assertEquals("UpdateTest",pub.getPublicationType());
		assertEquals(false,pub.getQualityAssured());
		assertEquals(false,pub.getConfidential());
		assertEquals(false,pub.getResearchOrProfCom());
		assertEquals("3",pub.getVolumeNumber());
		assertEquals("UpdateTest",pub.getArticleTitle());
		assertEquals("UpdateTest",pub.getDate());
		assertEquals("UpdateTest",pub.getUrl());
		assertEquals("UpdateTest",pub.getIssn());
		assertEquals("doi",pub.getDoi());
		assertEquals("publisher5",pub.getPublisher());
		assertEquals("This,is,a,test",pub.getKeywords());
		assertEquals("Location5",pub.getConferenceLocation());
		assertEquals("ConfDate5",pub.getConferenceDates());
		assertEquals("pubLocation5",pub.getPublisherLocation());
		assertEquals("edittor5,edittor4",pub.getEditors());
		assertEquals("5",pub.getEdition());
		assertEquals("5",pub.getJournalNumber());

		//Check that publication info has been successfully updated

    	assertEquals(1, pub.getAuthors().get(0).getAuthorId());
    	assertEquals("Mr", pub.getAuthors().get(0).getAuthorFirstName());
    	assertEquals("Gin", pub.getAuthors().get(0).getAuthorLastName());
    	assertEquals("The Black Organisation", pub.getAuthors().get(0).getOrganisation());
    	assertEquals("Retired", pub.getAuthors().get(0).getDepartment());
    	assertEquals("1", pub.getAuthors().get(0).getUserId());
    	//Check to see if the Author details on the only Author whose details should have changed, have been changed

		assertEquals(4,countTableRows("Author"));
    	assertEquals(3,countTableRows("Authors"));
    	assertEquals(1,countTableRows("Publication"));

    	dbTest.deleteAuthor(3);

		dbTest.delete("doi");
    	assertEquals(3,countTableRows("Author"));
    	assertEquals(0,countTableRows("Authors"));
    	assertEquals(0,countTableRows("Publication"));
    	//Test that delete works
    }

    @Test
    public void collectiveWriteTest() throws SQLException
    {
    	AuthorList authorList = new AuthorList();
		Author author1 = new Author();
		author1.setAuthorFirstName("author_f_Name1");
		author1.setAuthorLastName("author_l_Name1");
		author1.setOrganisation("organisation1");
		author1.setDepartment("department4");
		author1.setUserId("1");
		authorList.add(author1);

		Author author2 = new Author();
		author2.setAuthorFirstName("author_f_Name2");
		author2.setAuthorLastName("author_l_Name2");
		author2.setOrganisation("organisation2");
		author2.setDepartment("department4");
		author2.setUserId("2");
		authorList.add(author2);

    	Publication pub = new Publication();
    	pub.setPublicationId(1);
		pub.setDescription("description4");
		pub.setPublicationType("contributionType");
		pub.setPageNumbers("pageNumbers4");
		pub.setPublicationTitle("pubTitle4");
		pub.setQualityAssured(true);
		pub.setConfidential(true);
		pub.setResearchOrProfCom(true);
		pub.setVolumeNumber("4");
		pub.setArticleTitle("articleTitle4");
		pub.setDate("pubDate4");
		pub.setUrl("url4");
		pub.setIssn("issn4");
		pub.setDoi("doi");
		pub.setPublisher("publisher4");
		pub.setKeywords("This,is,a,test");
		pub.setConferenceLocation("Location4");
		pub.setConferenceDates("ConfDate4");
		pub.setPublisherLocation("pubLocation4");
		pub.setEditors("edittor4,edittor5");
		pub.setEdition("4");
		pub.setJournalNumber("4");
		pub.setAuthors(authorList);

    	dbTest.write(pub);
    	assertEquals(2,countTableRows("Author"));
    	assertEquals(2,countTableRows("Authors"));
    	assertEquals(1,countTableRows("Publication"));
    }

    @Test
    public void readTest() throws SQLException
    {
    	AuthorList authorList = new AuthorList();
		Author author1 = new Author();
		author1.setAuthorFirstName("author_f_Name1");
		author1.setAuthorLastName("author_l_Name1");
		author1.setOrganisation("organisation1");
		author1.setDepartment("department4");
		author1.setUserId("1");
		authorList.add(author1);

		Author author2 = new Author();
		author2.setAuthorFirstName("author_f_Name2");
		author2.setAuthorLastName("author_l_Name2");
		author2.setOrganisation("organisation2");
		author2.setDepartment("department4");
		author2.setUserId("2");
		authorList.add(author2);

    	Publication pub = new Publication();
    	pub.setPublicationId(1);
		pub.setDescription("description4");
		pub.setPublicationType("contributionType");
		pub.setPageNumbers("pageNumbers4");
		pub.setPublicationTitle("pubTitle4");
		pub.setQualityAssured(true);
		pub.setConfidential(true);
		pub.setResearchOrProfCom(true);
		pub.setVolumeNumber("4");
		pub.setArticleTitle("articleTitle4");
		pub.setDate("25/4/90");
		pub.setUrl("url4");
		pub.setIssn("1965-1232");
		pub.setDoi("doi");
		pub.setPublisher("publisher4");
		pub.setKeywords("This,is,a,test");
		pub.setConferenceLocation("Location4");
		pub.setConferenceDates("ConfDate4");
		pub.setPublisherLocation("pubLocation4");
		pub.setEditors("edittor4,edittor5");
		pub.setEdition("4");
		pub.setJournalNumber("4");
		pub.setAuthors(authorList);

    	try {
			dbTest.write(pub);
		}
    	catch (SQLException e)
		{
			// TODO Auto-generated catch block
			e.printStackTrace();
		}

    	//Add test data

    	Publication test = dbTest.read("doi");
    	assertEquals(test.getPublicationId(),1);
    	assertEquals(test.getDescription(), "description4");
    	assertEquals(test.getPublicationType(),"contributionType");
    	assertEquals(test.getPageNumbers(),"pageNumbers4");
    	assertEquals(test.getPublicationTitle(),"pubTitle4");
    	assertEquals(test.getQualityAssured(),true);
    	assertEquals(test.getConfidential(),true);
    	assertEquals(test.getResearchOrProfCom(),true);
    	assertEquals(test.getVolumeNumber(), "4");
    	assertEquals(test.getArticleTitle(),"articleTitle4");
    	assertEquals(test.getDate(),"25/4/90");
    	assertEquals(test.getUrl(),"url4");
    	assertEquals(test.getIssn(),"1965-1232");
    	assertEquals(test.getDoi(),"doi");

    	assertEquals(test.getAuthors().get(1).getAuthorId(),2);
    	assertEquals(test.getAuthors().get(1).getAuthorFirstName(),"author_f_Name2");
    	assertEquals(test.getAuthors().get(1).getAuthorLastName(),"author_l_Name2");
    	assertEquals(test.getAuthors().get(1).getOrganisation(),"organisation2");
    	assertEquals(test.getAuthors().get(1).getDepartment(),"department4");
    	assertEquals(test.getAuthors().get(1).getUserId(),"2");
    	assertEquals(test.getAuthors().get(0).getAuthorId(),1);
    	assertEquals(test.getAuthors().get(0).getAuthorFirstName(),"author_f_Name1");
    	assertEquals(test.getAuthors().get(0).getAuthorLastName(),"author_l_Name1");
    	assertEquals(test.getAuthors().get(0).getOrganisation(),"organisation1");
    	assertEquals(test.getAuthors().get(0).getDepartment(),"department4");
    	assertEquals(test.getAuthors().get(0).getUserId(),"1");
    	//Check stored values against expected values
    }

    @Test
    public void deleteTest() throws SQLException
    {
    	AuthorList authorList = new AuthorList();
		Author author1 = new Author();
		author1.setAuthorFirstName("author_f_Name1");
		author1.setAuthorLastName("author_l_Name1");
		author1.setOrganisation("organisation1");
		author1.setDepartment("department4");
		authorList.add(author1);

		Author author2 = new Author();
		author2.setAuthorFirstName("author_f_Name2");
		author2.setAuthorLastName("author_l_Name2");
		author2.setOrganisation("organisation2");
		author2.setDepartment("department4");
		authorList.add(author2);

    	Publication pub = new Publication();
    	pub.setPublicationId(1);
		pub.setDescription("description4");
		pub.setPublicationType("contributionType");
		pub.setPageNumbers("pageNumbers4");
		pub.setPublicationTitle("pubTitle4");
		pub.setQualityAssured(true);
		pub.setConfidential(true);
		pub.setResearchOrProfCom(true);
		pub.setVolumeNumber("4");
		pub.setArticleTitle("articleTitle4");
		pub.setDate("pubDate4");
		pub.setUrl("url4");
		pub.setIssn("issn4");
		pub.setDoi("doi");
		pub.setPublisher("publisher4");
		pub.setKeywords("This,is,a,test");
		pub.setConferenceLocation("Location4");
		pub.setConferenceDates("ConfDate4");
		pub.setPublisherLocation("pubLocation4");
		pub.setEditors("edittor4,edittor5");
		pub.setEdition("4");
		pub.setJournalNumber("4");
		pub.setAuthors(authorList);
		dbTest.write(pub);

		assertEquals(2,countTableRows("Author"));
    	assertEquals(2,countTableRows("Authors"));
    	assertEquals(1,countTableRows("Publication"));

    	pub = null;
    	pub = dbTest.read("doi");
    	assertEquals(1,pub.getPublicationId());

    	dbTest.delete(pub.getDoi());
    	assertEquals(2,countTableRows("Author"));
    	assertEquals(0,countTableRows("Authors"));
    	assertEquals(0,countTableRows("Publication"));
		//Test that the method returns true if the item is in the database
    }


    @Test
    public void getDoiListTest() throws SQLException
    {
    	PreparedStatement statement = connection.prepareStatement("INSERT INTO publication VALUES(?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)");
		statement.setString(1, "1");
		statement.setString(2, "Test");
		statement.setString(3, "Test");
		statement.setString(4, "Test");
		statement.setString(5, "Test");
		statement.setBoolean(6, true);
		statement.setBoolean(7, true);
		statement.setBoolean(8, true);
		statement.setInt(9, 5);
		statement.setString(10, "Test");
		statement.setString(11, "25/4/90");
		statement.setString(12, "Test");
		statement.setString(13, "Test");
		statement.setString(14, "TestDOI1");
		statement.setString(15, "publisher4");
		statement.setString(16, "This,is,a,test");
		statement.setString(17, "Location4");
		statement.setString(18, "ConfDate4");
		statement.setString(19, "pubLocation4");
		statement.setString(20, "edittor4,edittor5");
		statement.setString(21, "4");
		statement.setString(22, "4");
		statement.setString(23, "4");
		statement.execute();

		statement = connection.prepareStatement("INSERT INTO publication VALUES(?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)");
		statement.setString(1, "2");
		statement.setString(2, "Test");
		statement.setString(3, "Test");
		statement.setString(4, "Test");
		statement.setString(5, "Test");
		statement.setBoolean(6, true);
		statement.setBoolean(7, true);
		statement.setBoolean(8, true);
		statement.setInt(9, 5);
		statement.setString(10, "Test");
		statement.setString(11, "25/4/90");
		statement.setString(12, "Test");
		statement.setString(13, "Test");
		statement.setString(14, "TestDOI2");
		statement.setString(15, "publisher4");
		statement.setString(16, "This,is,a,test");
		statement.setString(17, "Location4");
		statement.setString(18, "ConfDate4");
		statement.setString(19, "pubLocation4");
		statement.setString(20, "edittor4,edittor5");
		statement.setString(21, "4");
		statement.setString(22, "4");
		statement.setString(23, "4");
		statement.execute();

		statement = connection.prepareStatement("INSERT INTO publication VALUES(?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)");
		statement.setString(1, "3");
		statement.setString(2, "Test");
		statement.setString(3, "Test");
		statement.setString(4, "Test");
		statement.setString(5, "Test");
		statement.setBoolean(6, true);
		statement.setBoolean(7, true);
		statement.setBoolean(8, true);
		statement.setInt(9, 5);
		statement.setString(10, "Test");
		statement.setString(11, "25/4/90");
		statement.setString(12, "Test");
		statement.setString(13, "Test");
		statement.setString(14, "TestDOI3");
		statement.setString(15, "publisher4");
		statement.setString(16, "This,is,a,test");
		statement.setString(17, "Location4");
		statement.setString(18, "ConfDate4");
		statement.setString(19, "pubLocation4");
		statement.setString(20, "edittor4,edittor5");
		statement.setString(21, "4");
		statement.setString(22, "4");
		statement.setString(23, "4");
		statement.execute();
    	assertEquals(dbTest.getDoiList().length,3);
    }

    @Test
    public void writeAuthorTest() throws SQLException
    {
    	Author author1 = new Author();
		author1.setAuthorFirstName("TestFirstName1");
		author1.setAuthorLastName("TestLastName1");
		author1.setOrganisation("organisation1");
		author1.setDepartment("department4");

		assertEquals(0,countTableRows("author"));
		dbTest.writeAuthor(author1);
		assertEquals(1,countTableRows("author"));

		Author author2 = new Author();
		author2.setAuthorFirstName("TestFirstName2");
		author2.setAuthorLastName("TestLastName2");
		author2.setOrganisation("organisation2");
		author2.setDepartment("department4");
		dbTest.writeAuthor(author2);
		assertEquals(2,countTableRows("author"));

		author2 = new Author();
		author2.setAuthorFirstName("TestFirstName2");
		author2.setAuthorLastName("TestLastName2");
		author2.setOrganisation("organisation2");
		author2.setDepartment("department5");
		dbTest.writeAuthor(author2);
		assertEquals(2,countTableRows("author"));
    }

    @Test
    public void deleteAuthor() throws SQLException
    {
    	Author author1 = new Author();
		author1.setAuthorFirstName("TestFirstName1");
		author1.setAuthorLastName("TestLastName1");
		author1.setOrganisation("organisation1");
		author1.setDepartment("department4");

		Author author2 = new Author();
		author2.setAuthorFirstName("TestFirstName2");
		author2.setAuthorLastName("TestLastName2");
		author2.setOrganisation("organisation2");
		author2.setDepartment("department4");

		dbTest.writeAuthor(author1);
		dbTest.writeAuthor(author2);
		assertEquals(2,countTableRows("author"));

		dbTest.deleteAuthor(1);
		assertEquals(1,countTableRows("author"));

		dbTest.deleteAuthor(2);
		assertEquals(0,countTableRows("author"));
    }

    @Test
    public void updateTest() throws SQLException
    {
    	AuthorList authorList = new AuthorList();
		Author author1 = new Author();
		author1.setAuthorFirstName("TestFirstName1");
		author1.setAuthorLastName("TestLastName1");
		author1.setOrganisation("organisation1");
		author1.setDepartment("department4");
		authorList.add(author1);

		Author author2 = new Author();
		author2.setAuthorFirstName("TestFirstName2");
		author2.setAuthorLastName("TestLastName2");
		author2.setOrganisation("organisation2");
		author2.setDepartment("department4");
		authorList.add(author2);

    	Publication pub = new Publication();
    	pub.setPublicationId(1);
		pub.setDescription("description4");
		pub.setPublicationType("contributionType");
		pub.setPageNumbers("pageNumbers4");
		pub.setPublicationTitle("pubTitle4");
		pub.setQualityAssured(true);
		pub.setConfidential(true);
		pub.setResearchOrProfCom(true);
		pub.setVolumeNumber("4");
		pub.setArticleTitle("articleTitle4");
		pub.setDate("pubDate4");
		pub.setUrl("url4");
		pub.setIssn("issn4");
		pub.setDoi("doi");

		pub.setPublisher("publisher4");
		pub.setKeywords("This,is,a,test");
		pub.setConferenceLocation("Location4");
		pub.setConferenceDates("ConfDate4");
		pub.setPublisherLocation("pubLocation4");
		pub.setEditors("edittor4,edittor5");
		pub.setEdition("4");
		pub.setJournalNumber("4");
		pub.setAuthors(authorList);
		dbTest.write(pub);
		assertEquals(dbTest.read(pub.getDoi()).getPublicationId(),pub.getPublicationId());
		assertEquals(dbTest.read(pub.getDoi()).getDoi(),pub.getDoi());
		assertEquals(2,countTableRows("Author"));
    	assertEquals(2,countTableRows("Authors"));
    	assertEquals(1,countTableRows("Publication"));

		Publication pub2 = new Publication();
		authorList = null;
		//Case 3. The author exists, as does the relationship with publication. We want to update author info in this case
    	authorList = new AuthorList();
		author1 = new Author();
		author1.setAuthorFirstName("TestFirstName1");
		author1.setAuthorLastName("TestLastName1");
		author1.setOrganisation("organisation1");
		author1.setDepartment("case3Test");
		authorList.add(author1);

		//Case 4. Removing an existing author

		//Case 1. Add a author that doesn't yet exist in the author table to check this case works
		Author author3 = new Author();
		author3.setAuthorFirstName("TestFirstName3");
		author3.setAuthorLastName("TestLastName3");
		author3.setOrganisation("organisation3");
		author3.setDepartment("department3");
		authorList.add(author3);

		//Case 2. A author that exists within the author table, but does not have a relationship with the current publication. i.e. no entry for this particular pubID,authorID.
		Author author4 = new Author();
		author4.setAuthorFirstName("TestFirstName4");
		author4.setAuthorLastName("TestLastName4");
		author4.setOrganisation("organisation4");
		author4.setDepartment("department4");
		authorList.add(author4);

		PreparedStatement preparedStatement = connection.prepareStatement("INSERT INTO author (authorFirstName,authorLastName,organisation,department) VALUES(?,?,?,?)");
		preparedStatement.setString(1, author4.getAuthorFirstName());
		preparedStatement.setString(2, author4.getAuthorLastName());
		preparedStatement.setString(3, author4.getOrganisation());
		preparedStatement.setString(4, author4.getDepartment());
		preparedStatement.execute();

		assertEquals(3,countTableRows("author"));

		pub2.setPublicationId(1);
		pub2.setDescription("UpdateTest");
		pub2.setPublicationType("UpdateTest");
		pub2.setPageNumbers("UpdateTest");
		pub2.setPublicationTitle("UpdateTest");
		pub2.setQualityAssured(false);
		pub2.setConfidential(false);
		pub2.setResearchOrProfCom(false);
		pub2.setVolumeNumber("3");
		pub2.setArticleTitle("UpdateTest");
		pub2.setDate("UpdateTest");
		pub2.setUrl("UpdateTest");
		pub2.setIssn("UpdateTest");
		pub2.setDoi("doi");
		pub2.setAuthors(authorList);
		dbTest.write(pub2);

		pub = dbTest.read("doi");

		assertEquals(pub.getPublicationId(),1);
		assertEquals(pub.getDoi(),"doi");
		//Tests that publication info is updated
		assertEquals(pub.getDescription(),"UpdateTest");

		//Tests that case 1. i.e. new Authors are added
		assertEquals(4,countTableRows("Author"));
		//Tests that case 1 & case 2 & case 4 work. Case 1 = new author + new relationship, Case 2 = existing author, new relationship. Case 4 = removal of authors no longer linked to publication
    	assertEquals(3,countTableRows("Authors"));
    	assertEquals(1,countTableRows("Publication"));

    	//Test that case 3 works. Case 3 = Update info of existing author
    	assertEquals("TestFirstName1", pub.getAuthors().get(0).getAuthorFirstName());
    //	assertEquals("case3Test", pub.getAuthors().get(0).getRimsUserId());

    }

  //A few methods used solely for testing purposes

    public int countTableRows(String table) throws SQLException
    {
    	Statement statement = connection.createStatement();
    	int count = 0;
    	ResultSet resultSet = statement.executeQuery("SELECT COUNT(*) FROM "+table);
    	while (resultSet.next())
        {
          count = resultSet.getInt(1);
        }
    	resultSet.close();
		return count;
    }
}
